package mudbconnection;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import mumethod.Base64Object;
import mumethod.LibFunction;
import muobject.Student;
import muobject.User;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

@Component("DBStudentsService")
public class DBStudentsService implements DBStudents {

	private JdbcTemplate jdbct;
	private Base64Object base64Object = new LibFunction();

	@Autowired
	public DBStudentsService(DataSource jdbct) {
		super();
		this.jdbct = new JdbcTemplate(jdbct);
	}

	public List<Student> getAllStudents() {
		try {
			@SuppressWarnings("unchecked")
			List<Student> students = (List<Student>) jdbct.query(
					"Select * from Students ", new StudentMapper());
			return students;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	public List<Student> getSpicalStudents(String name, String value, String sno, String year) {
		try {
			@SuppressWarnings("unchecked")
			List<Student> students = (List<Student>) jdbct.query(
					"Select * from Students where " + name + " like ? and Years like ? and sno like ?",
					new Object[] { value, year, sno }, new StudentMapper());
			return students;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public List<Student> getAssignStudents(String sno, String year,
			String grade, String sclass, String stno) {
		try {
			@SuppressWarnings("unchecked")
			List<Student> students = (List<Student>) jdbct.query(
					"Select * from Students where Years like ? and Sno=? and Grade like ? and Sclass like ? and stno like ?",
					new Object[] { year, sno, grade, sclass, stno }, new StudentMapper());
			return students;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	public List<Student> getSpicalYearStudents(String sno, String year,
			String grade, String sclass) {
		try {
			@SuppressWarnings("unchecked")
			List<Student> students = (List<Student>) jdbct
					.query("Select * from Students where Years like ? and Sno=? and Grade like ? and Sclass like ?",
							new Object[] { year, sno, grade, sclass },
							new StudentMapper());
			return students;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	@SuppressWarnings("rawtypes")
	public List getAllYear(String sno) {
		try {
			List years = jdbct
					.queryForList("Select Years from Students group by Years");
			return years;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	@SuppressWarnings("rawtypes")
	public List getAllGrade(String sno, String year, String sclass) {
		try {
			List grades = jdbct
					.queryForList(
							"Select Grade from Students where Years like ? and Sclass like ? group by Grade",
							new Object[] { year, sclass });
			return grades;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	@SuppressWarnings("rawtypes")
	public List getAllSclass(String sno, String year, String grade) {
		try {
			List sclasses = jdbct
					.queryForList(
							"Select Sclass from Students where Years like ? and Grade like ? group by Sclass",
							new Object[] { year, grade });
			return sclasses;
		} catch (Exception e) {
			System.out.println(e.getMessage());
			return null;
		}
	}

	public boolean setStudent(Student student) {
		try{
			jdbct.update("insert into Students(stno, sno, grade, sclass, classno, ename, cname, sex, birthday, years, password) " +
					"values(?,?,?,?,?,?,?,?,?,?,?)", 
				     new Object[] {student.getStno(), student.getSno(), student.getGrade(), student.getSclass(),
	                   student.getClassno(), student.getEname(), student.getCname(),
	                   student.getSex(), String.valueOf(student.getBirthday()), student.getYears(), student.getPassword()});
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public boolean updateStudent(Student student){
		try{
			jdbct.update("update Students set stno=?, sno=?, grade=?, sclass=?, classno=?, ename=?, cname=?, sex=?, birthday=?, " +
					"years=?, password=?, updatedate=? where stno=? and years=?", 
				     new Object[] {student.getStno(), student.getSno(), student.getGrade(), student.getSclass(),
	                   String.valueOf(student.getClassno()), student.getEname(), student.getCname(),
	                   student.getSex(), String.valueOf(student.getBirthday()), student.getYears(), student.getPassword(),
	                   student.getUpdatedate(), student.getStno(), student.getYears()});
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}
	
	public boolean deleteStudentByStno(String stno){
		try{
			jdbct.update("delete from Students where stno=? ", 
				     new Object[] {stno});
			return true;
		}catch(Exception e){
			System.out.println(e.getMessage());
			return false;
		}
	}

	class StudentMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Student student = null;
			try {
				student = new Student(rs.getInt("stid"), rs.getString("stno"),
						rs.getString("sno"), rs.getString("grade"),
						rs.getString("sclass"), rs.getInt("classno"),
						rs.getString("ename"), rs.getString("cname"),
						rs.getString("sex"), rs.getDate("birthday"),
						rs.getString("years"), rs.getString("password"),
						rs.getDate("createdate"), rs.getDate("updatedate"));
			} catch (Exception e) {
				System.out.println(e.getMessage());
			}
			return student;
		}
	}
}
